package gth.news.dao;

import gth.news.domain.News;
import gth.utils.DatabaseUtils;

import java.beans.PropertyVetoException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class NewsRepository {

    /**
     * 查询全部
     * @return
     */
    public List<News> findAll() throws PropertyVetoException, SQLException {
        ResultSet resultSet=null;
        Statement stmt=null;
        Connection conn = DatabaseUtils.getConnection();
        List<News> newsList = new ArrayList<>();
        try {
            stmt = conn.createStatement();
            String sql = "select id,title,content,insertDate,updateDate from news n";
            resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                Integer id = resultSet.getInt(1);
                String title = resultSet.getString(2);
                String content = resultSet.getString(3);
                Date insertDate = resultSet.getDate(4);
                Date updateDate = resultSet.getDate(5);
                News news = new News(id, title, content, insertDate, updateDate);
                newsList.add(news);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                resultSet = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
        }
        return newsList;
    }

    /**
     * @desc 根据id查找
     * @param id
     * @return
     */
    public News findById(Integer id) throws PropertyVetoException, SQLException {
        Connection conn = DatabaseUtils.getConnection();
        ResultSet resultSet = null;
        PreparedStatement stmt = null;
        try {
            String sql = "select id,title,content,insertDate,updateDate from news n where n.id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1,id);
            resultSet = stmt.executeQuery();
            while (resultSet.next()) {
                Integer nid = resultSet.getInt(1);
                String title = resultSet.getString(2);
                String content = resultSet.getString(3);
                Date insertDate = resultSet.getDate(4);
                Date updateDate = resultSet.getDate(5);
                return new News(nid, title, content, insertDate, updateDate);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                resultSet = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
        }
        return null;
    }
}
